/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2000                    */
/* Created on:     2012/5/17 11:16:18                           */
/*==============================================================*/
use GameCode
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('AccessLog') and o.name = 'FK_ACCESSLO_REFERENCE_DEVICE')
alter table AccessLog
   drop constraint FK_ACCESSLO_REFERENCE_DEVICE
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Advertise') and o.name = 'FK_ADVERTIS_REFERENCE_GAMEPROJ')
alter table Advertise
   drop constraint FK_ADVERTIS_REFERENCE_GAMEPROJ
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Code') and o.name = 'FK_CODE_REFERENCE_GAMEPROJ')
alter table Code
   drop constraint FK_CODE_REFERENCE_GAMEPROJ
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Code') and o.name = 'FK_CODE_REFERENCE_DEVICE')
alter table Code
   drop constraint FK_CODE_REFERENCE_DEVICE
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('GameRecommend') and o.name = 'FK_GAMERECO_REFERENCE_GAMEPROJ')
alter table GameRecommend
   drop constraint FK_GAMERECO_REFERENCE_GAMEPROJ
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('GameRefGameType') and o.name = 'FK_GAMEREFG_FK_GAMERE_GAMETYPE')
alter table GameRefGameType
   drop constraint FK_GAMEREFG_FK_GAMERE_GAMETYPE
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('GameRefGameType') and o.name = 'FK_GAMEREFG_REFERENCE_GAMEPROJ')
alter table GameRefGameType
   drop constraint FK_GAMEREFG_REFERENCE_GAMEPROJ
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('HitLog') and o.name = 'FK_HITLOG_REFERENCE_DEVICE')
alter table HitLog
   drop constraint FK_HITLOG_REFERENCE_DEVICE
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('NewArea') and o.name = 'FK_NEWAREA_REFERENCE_GAMEPROJ')
alter table NewArea
   drop constraint FK_NEWAREA_REFERENCE_GAMEPROJ
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Push') and o.name = 'FK_PUSH_REFERENCE_GAMEPROJ')
alter table Push
   drop constraint FK_PUSH_REFERENCE_GAMEPROJ
go

if exists (select 1
            from  sysobjects
           where  id = object_id('AccessLog')
            and   type = 'U')
   drop table AccessLog
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Advertise')
            and   type = 'U')
   drop table Advertise
go

if exists (select 1
            from  sysobjects
           where  id = object_id('AndriodApp')
            and   type = 'U')
   drop table AndriodApp
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Code')
            and   type = 'U')
   drop table Code
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Device')
            and   type = 'U')
   drop table Device
go

if exists (select 1
            from  sysobjects
           where  id = object_id('GameProject')
            and   type = 'U')
   drop table GameProject
go

if exists (select 1
            from  sysobjects
           where  id = object_id('GameRecommend')
            and   type = 'U')
   drop table GameRecommend
go

if exists (select 1
            from  sysobjects
           where  id = object_id('GameRefGameType')
            and   type = 'U')
   drop table GameRefGameType
go

if exists (select 1
            from  sysobjects
           where  id = object_id('GameType')
            and   type = 'U')
   drop table GameType
go

if exists (select 1
            from  sysobjects
           where  id = object_id('HitLog')
            and   type = 'U')
   drop table HitLog
go

if exists (select 1
            from  sysobjects
           where  id = object_id('NewArea')
            and   type = 'U')
   drop table NewArea
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PageElement')
            and   type = 'U')
   drop table PageElement
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Push')
            and   type = 'U')
   drop table Push
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SystemArticle')
            and   type = 'U')
   drop table SystemArticle
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SystemData')
            and   type = 'U')
   drop table SystemData
go

/*==============================================================*/
/* Table: AccessLog                                             */
/*==============================================================*/
create table AccessLog (
   ID                   uniqueidentifier     not null,
   DeviceID             uniqueidentifier     not null,
   DateTime             datetime             not null,
   constraint PK_ACCESSLOG primary key (ID)
)
go

/*==============================================================*/
/* Table: Advertise                                             */
/*==============================================================*/
create table Advertise (
   ID                   uniqueidentifier     not null,
   Name                 varchar(500)         not null,
   ImageLink            varchar(500)         not null,
   BigImageLink         varchar(500)         not null,
   DateTime             varchar(50)          not null,
   Effective            bit                  not null,
   ProjectID            uniqueidentifier     not null,
   Seq                  int                  not null,
   constraint PK_ADVERTISE primary key (ID)
)
go

/*==============================================================*/
/* Table: AndriodApp                                            */
/*==============================================================*/
create table AndriodApp (
   ID                   uniqueidentifier     not null,
   Version              varchar(50)          not null,
   Link                 varchar(200)         not null,
   constraint PK_ANDRIODAPP primary key (ID)
)
go

/*==============================================================*/
/* Table: Code                                                  */
/*==============================================================*/
create table Code (
   ID                   uniqueidentifier     not null,
   ProjectID            uniqueidentifier     not null,
   Code                 varchar(50)          not null,
   Seq                  int                  not null,
   DeviceID             uniqueidentifier     null,
   FetchDate            datetime             null,
   constraint PK_CODE primary key (ID)
)
go

/*==============================================================*/
/* Table: Device                                                */
/*==============================================================*/
create table Device (
   ID                   uniqueidentifier     not null,
   UUID                 varchar(50)          not null,
   Type                 varchar(10)          not null,
   Phone                varchar(20)          not null,
   InstallDate          datetime             not null,
   LastAccessDate       datetime             not null,
   constraint PK_DEVICE primary key (ID)
)
go

/*==============================================================*/
/* Table: GameProject                                           */
/*==============================================================*/
create table GameProject (
   ID                   uniqueidentifier     not null,
   RightName            varchar(50)          not null,
   GameName             varchar(50)          not null,
   GameDescription      varchar(50)          not null,
   LogoLink             varchar(500)         not null,
   ProjectNr            varchar(10)          not null,
   ProjectName          varchar(50)          not null,
   Operator             varchar(500)         not null,
   ServerName           varchar(50)          not null,
   OperateState         varchar(50)          not null,
   WebSite              varchar(50)          not null,
   OpenDate             varchar(50)          not null,
   GiftContent          text                 not null,
   Seq                  int                  not null,
   Effective            bit                  not null,
   constraint PK_GAMEPROJECT primary key (ID)
)
go

/*==============================================================*/
/* Table: GameRecommend                                         */
/*==============================================================*/
create table GameRecommend (
   ID                   uniqueidentifier     not null,
   ProjectID            uniqueidentifier     not null,
   Seq                  int                  not null,
   constraint PK_GAMERECOMMEND primary key (ID)
)
go

/*==============================================================*/
/* Table: GameRefGameType                                       */
/*==============================================================*/
create table GameRefGameType (
   ID                   uniqueidentifier     not null,
   GameTypeID           uniqueidentifier     not null,
   GameProjectID        uniqueidentifier     not null,
   constraint PK_GAMEREFGAMETYPE primary key (ID)
)
go

/*==============================================================*/
/* Table: GameType                                              */
/*==============================================================*/
create table GameType (
   ID                   uniqueidentifier     not null,
   Name                 varchar(50)          not null,
   Description          varchar(1000)        not null,
   ImageLink            varchar(50)          not null,
   Seq                  int                  not null,
   constraint PK_GAMETYPE primary key (ID)
)
go

/*==============================================================*/
/* Table: HitLog                                                */
/*==============================================================*/
create table HitLog (
   ID                   uniqueidentifier     not null,
   DeviceID             uniqueidentifier     not null,
   SourceID             uniqueidentifier     not null,
   SourceType           varchar(10)          not null,
   DateTime             datetime             not null,
   constraint PK_HITLOG primary key (ID)
)
go

/*==============================================================*/
/* Table: NewArea                                               */
/*==============================================================*/
create table NewArea (
   ID                   uniqueidentifier     not null,
   ProjectID            uniqueidentifier     not null,
   StartDate            varchar(50)          not null,
   ServerName           varchar(50)          not null,
   Operator             varchar(50)          not null,
   Seq                  integer              not null,
   Effective            bit                  not null,
   constraint PK_NEWAREA primary key (ID)
)
go

/*==============================================================*/
/* Table: PageElement                                           */
/*==============================================================*/
create table PageElement (
   ID                   uniqueidentifier     not null,
   Type                 char(10)             not null,
   Content              varchar(200)         not null,
   Link                 varchar(200)         not null,
   ImageLink            varchar(200)         not null,
   Seq                  int                  not null,
   constraint PK_PAGEELEMENT primary key (ID)
)
go

/*==============================================================*/
/* Table: Push                                                  */
/*==============================================================*/
create table Push (
   ID                   uniqueidentifier     not null,
   DateTime             datetime             not null,
   Content              varchar(500)         not null,
   ProjectID            uniqueidentifier     not null,
   constraint PK_PUSH primary key (ID)
)
go

/*==============================================================*/
/* Table: SystemArticle                                         */
/*==============================================================*/
create table SystemArticle (
   ID                   uniqueidentifier     not null,
   Name                 varchar(100)         not null,
   Type                 varchar(30)          not null,
   Content              text                 not null,
   Seq                  int                  not null,
   DateTime             datetime             not null,
   constraint PK_SYSTEMARTICLE primary key (ID)
)
go

/*==============================================================*/
/* Table: SystemData                                            */
/*==============================================================*/
create table SystemData (
   Name                 varchar(50)          not null,
   Value                varchar(500)         not null,
   Value2               varchar(500)         null,
   constraint PK_SYSTEMDATA primary key (Name)
)
go

alter table AccessLog
   add constraint FK_ACCESSLO_REFERENCE_DEVICE foreign key (DeviceID)
      references Device (ID)
go

alter table Advertise
   add constraint FK_ADVERTIS_REFERENCE_GAMEPROJ foreign key (ProjectID)
      references GameProject (ID)
go

alter table Code
   add constraint FK_CODE_REFERENCE_GAMEPROJ foreign key (ProjectID)
      references GameProject (ID)
go

alter table Code
   add constraint FK_CODE_REFERENCE_DEVICE foreign key (DeviceID)
      references Device (ID)
go

alter table GameRecommend
   add constraint FK_GAMERECO_REFERENCE_GAMEPROJ foreign key (ProjectID)
      references GameProject (ID)
go

alter table GameRefGameType
   add constraint FK_GAMEREFG_FK_GAMERE_GAMETYPE foreign key (GameTypeID)
      references GameType (ID)
go

alter table GameRefGameType
   add constraint FK_GAMEREFG_REFERENCE_GAMEPROJ foreign key (GameProjectID)
      references GameProject (ID)
go

alter table HitLog
   add constraint FK_HITLOG_REFERENCE_DEVICE foreign key (DeviceID)
      references Device (ID)
go

alter table NewArea
   add constraint FK_NEWAREA_REFERENCE_GAMEPROJ foreign key (ProjectID)
      references GameProject (ID)
go

alter table Push
   add constraint FK_PUSH_REFERENCE_GAMEPROJ foreign key (ProjectID)
      references GameProject (ID)
go

